import pandas as pd
from sqlalchemy import create_engine, text


db_config = {
        'host': '127.0.0.1',
        'port': 3306,
        'user': 'chess',
        'password': 'Lichess2022',
        'database': 'chess'
    }

# def import_puzzles(csv_file, db_config, batch_size=2000):
#     # 创建数据库连接
#     engine = create_engine(
#         f"mysql+mysqlconnector://{db_config['user']}:{db_config['password']}@"
#         f"{db_config['host']}:{db_config['port']}/{db_config['database']}"
#     )
    
#     # 使用 chunksize 参数分批读取 CSV 文件
#     total_rows = 0
#     for chunk in pd.read_csv(csv_file, chunksize=batch_size):
#         chunk.to_sql('lichesspuzzle', engine, if_exists='append', index=False)
#         total_rows += len(chunk)
#         print(f"已导入 {total_rows} 条记录")
    
#     print(f"导入完成，总共导入 {total_rows} 条记录")



def choose_puzzle(rating, count):
    """
    从数据库中随机选择指定难度范围的题目
    
    Args:
        rating: 目标难度评分
        count: 需要返回的题目数量，最大20
        db_config: 数据库配置信息
    
    Returns:
        DataFrame: 包含选中题目的数据框
    """
    # 确保 count 不超过20
    count = min(count, 20)
    
    # 计算评分范围
    rating_min = rating - 50
    rating_max = rating + 50
    
    # 创建数据库连接
    engine = create_engine(
        f"mysql+mysqlconnector://{db_config['user']}:{db_config['password']}@"
        f"{db_config['host']}:{db_config['port']}/{db_config['database']}"
    )
    
    # 构建SQL查询
    sql = text("""
        SELECT PuzzleId,FEN,Rating,GameUrl,Moves FROM lichesspuzzle
        WHERE PuzzleId in 
            (SELECT PuzzleId FROM 
                (SELECT PuzzleId FROM lichesspuzzle 
                 WHERE rating >= :rating_min AND rating <= :rating_max
                 ORDER BY RAND() 
                 LIMIT :inner_limit) AS t)
        LIMIT :count
    """)
    
    # 执行查询
    with engine.connect() as conn:
        result = pd.read_sql(
            sql,
            conn,
            params={
                'rating_min': rating_min,
                'rating_max': rating_max,
                'inner_limit': count * 3,  # 为了增加随机性，内部选择更多的记录
                'count': count
            }
        )
    
    return result.to_dict('records')  # 转换为字典列表

if __name__ == "__main__":
    # 数据库配置
   
    
    # CSV 文件路径
    # csv_file = 'lichess_puzzles.csv'
    
    # try:
    #     import_puzzles(csv_file, db_config)
    # except Exception as e:
    #     print(f"导入过程中出现错误: {str(e)}") 
    
    # 示例：选择难度在1000左右的10个题目
    try:
        puzzles = choose_puzzle(1000, 4)
        print(f"获取到 {len(puzzles)} 个题目：")
        
        # 假设我们有一个函数可以生成SVG
        svg_files = []
        for puzzle in puzzles:
            # 这里需要替换为实际生成SVG的代码
            svg_content = f'<svg width="400" height="400" xmlns="http://www.w3.org/2000/svg">...</svg>'
            svg_files.append(svg_content)
        
        # 合并SVG（2x2布局）
        merge_svgs(svg_files, 'merged_puzzles.svg', layout=(2, 2))
        print("SVG文件已合并并保存为 merged_puzzles.svg")
        
        # 打印题目信息
        for puzzle in puzzles:
            print("\n题目信息:")
            print(f"PuzzleId: {puzzle['PuzzleId']}")
            print(f"FEN: {puzzle['FEN']}")
            print(f"Rating: {puzzle['Rating']}")
            print(f"GameUrl: {puzzle['GameUrl']}")
            print(f"Moves: {puzzle['Moves']}")
            print("-" * 50)
    except Exception as e:
        print(f"处理过程中出现错误: {str(e)}") 